package com.weavernorth.salary;

import java.sql.*;
import java.util.*;

import com.weavernorth.util.Logger4jUtil;
import oracle.jdbc.OracleTypes;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.log4j.Logger;
import weaver.conn.RecordSet;
import weaver.formmode.setup.ModeRightInfo;
import weaver.general.BaseBean;
import weaver.general.StaticObj;
import weaver.general.Util;
import weaver.interfaces.datasource.DataSource;

public class GetV8MessageUtil {
//    public static Log log = LogFactory.getLog(GetV8MessageUtil.class.getName());
Logger log = Logger4jUtil.getLoggerByName("/action/api/salary/GetV8MessForParam/");

    /**
     * 调用V8计算薪资的存储过程
     *
     * @param strYear
     * @param strMonth
     * @param strDm
     * @throws Exception
     */
    public List<MessForV8Vo> getV8Mess(String strYear, String strMonth, String strDm, String strDpid, RecordSet rsSelect) throws Exception {

        CallableStatement callStmt = null;
        new BaseBean().writeLog("进入存储过程:" + strYear + "," + strMonth + "," + strDm);
        List<MessForV8Vo> listMessV8 = new ArrayList<MessForV8Vo>();

        // 获取sqlserver的数据库连接
        DataSource ds = (DataSource) StaticObj.getServiceByFullname(
                ("datasource.V8A"), DataSource.class);
        Connection conn = ds.getConnection();
        try {
            // 存储过程 P_ERDOS_YJBB_WL_QRY 查询数据
            callStmt = conn.prepareCall("{call fyerp.P_ERDOS_YJBB_WL_QRY(?,?,?,?,?,?)}");
            // 参数index从1开始，依次 1,2,3...
//            callStmt.setString(1, "");
//            callStmt.setString(2, "");
//            callStmt.setString(3, "");
            callStmt.setString(1, strYear);
            callStmt.setString(2, strMonth);
            callStmt.setString(3, strDm);
            callStmt.registerOutParameter(4, Types.FLOAT);
            callStmt.registerOutParameter(5, Types.VARCHAR);
            callStmt.registerOutParameter(6, OracleTypes.CURSOR);
            callStmt.execute();
            //String testPrint = callStmt.getString(4);
            //获取信息结果集
            ResultSet rs = (ResultSet) callStmt.getObject(6);
            //循环结果集
            while (rs.next()) {
                MessForV8Vo messForV8Vo = new MessForV8Vo();
                //sap店铺id
                String strSapid = Util.null2String(rs.getString(1));
                messForV8Vo.setStrSapid(strSapid);
                //员工编码
                String strWorkcode = Util.null2String(rs.getString(2));
                log.info("员工编码:" + strWorkcode);
                messForV8Vo.setStrWorkcode(strWorkcode);
                //员工编码查询岗位id和岗位id
                Map mapUser = getJobMess(strWorkcode, rsSelect);
                if (mapUser != null) {
                    //岗位id
                    String strJobid = Util.null2String(mapUser.get("jobid"));
                    messForV8Vo.setStrJobid(strJobid);
                    //人员id
                    String strUserid = Util.null2String(mapUser.get("id"));
                    messForV8Vo.setStrUserid(strUserid);
                    //人员名称
                    String strUserName = Util.null2String(mapUser.get("lastname"));
                    messForV8Vo.setStrUserName(strUserName);
                    //岗位名称
                    String strJobName = Util.null2String(mapUser.get("jobname"));
                    messForV8Vo.setStrJobName(strJobName);
                }
                //月任务
                String strMonthSales = Util.null2String(rs.getString(5));
                messForV8Vo.setStrMonthSales(strMonthSales);
                //销售总流水
                String strCountSales = Util.null2String(rs.getString(6));
                messForV8Vo.setStrCountSales(strCountSales);
                //支援总流水
                String strSupCountSales = Util.null2String(rs.getString(7));
                messForV8Vo.setStrSupCountSales(strSupCountSales);
                //正价销售
                String strPriceSale = Util.null2String(rs.getString(8));
                messForV8Vo.setStrPriceSale(strPriceSale);
                //支援正价
                String strSupValSale = Util.null2String(rs.getString(9));
                messForV8Vo.setStrSupValSale(strSupValSale);
                //折价销售
                String strDiscountSale = Util.null2String(rs.getString(10));
                messForV8Vo.setStrDiscountSale(strDiscountSale);
                //支援折价
                String strSupDisSale = Util.null2String(rs.getString(11));
                messForV8Vo.setStrSupDisSale(strSupDisSale);
                //是否汇总数据
                String strCountFlag = Util.null2String(rs.getString(16));
                //Y为汇总 ,N为非汇总
                if ("Y".equals(strCountFlag)) {
                    messForV8Vo.setStrCountFlag("0");
                } else {
                    messForV8Vo.setStrCountFlag("1");
                }
                //岗位代码
                String strJobcode = Util.null2String(rs.getString(4));

                log.info("岗位代码:" + strJobcode);
                messForV8Vo.setStrJobCode(strJobcode);
                //店铺id
                messForV8Vo.setStrDpid(strDpid);
                //团购正价
                String strTgzj = Util.null2String(rs.getString(12));
                messForV8Vo.setStrTgzj(strTgzj);
                //团购折价
                String strTgzhej = Util.null2String(rs.getString(13));
                messForV8Vo.setStrTgzhej(strTgzhej);
                //微销正价
                String strWxzj=Util.null2String(rs.getString(14));
                messForV8Vo.setStrWxzj(strWxzj);
                //微销折价
                String strWxzhej=Util.null2String(rs.getString(15));
                messForV8Vo.setStrWxzhej(strWxzhej);

                //员工所属店铺编码
                String strYgssDpCode=Util.null2String(rs.getString(17));
                messForV8Vo.setStrYgssDpCode(strYgssDpCode);

                //实收总流水
                String strSszls=Util.null2String(rs.getString(18));
                messForV8Vo.setStrSszls(strSszls);
                //实收正价流水
                String strSszjls=Util.null2String(rs.getString(19));
                messForV8Vo.setStrSszjls(strSszjls);
                //实收折价流水
                String strSszhjls=Util.null2String(rs.getString(20));
                messForV8Vo.setStrSszhjls(strSszhjls);
                //封装list
                listMessV8.add(messForV8Vo);
            }
        } catch (Exception e) {
            e.printStackTrace();
            new BaseBean().writeLog("执行存储过程发生异常>>>>>>>>" + e.toString());
        } finally {
            if (null != callStmt) {
                callStmt.close();
            }
            if (null != conn) {
                conn.close();
            }
        }

        return listMessV8;
    }

    /**
     * @param strWorkcode 员工编码
     * @return map, 员工id和员工岗位
     */

    public Map getJobMess(String strWorkcode, RecordSet rs) {
        //返回结果集
        Map mapUser = new HashMap();
        String sql = "select hr.id,hr.jobtitle,hr.lastname ,hj.jobtitlename from hrmresource hr,hrmjobtitles hj where hr.jobtitle=hj.id and" +
                " hr.workcode=? and (hr.accounttype is null or hr.accounttype != '1') and hr.status<7";
        log.info("查询岗位和id:" + sql + ",workcode:" + strWorkcode);
        rs.executeQuery(sql, strWorkcode);
        if (rs.next()) {
            mapUser.put("jobid", Util.null2String(rs.getString("jobtitle")));
            mapUser.put("id", Util.null2String(rs.getString("id")));
            mapUser.put("lastname", Util.null2String(rs.getString("lastname")));
            mapUser.put("jobname", Util.null2String(rs.getString("jobtitlename")));
        }

        return mapUser;

    }

    /**
     * 插入/更新建模表uf_xsyjhzb
     */
    //2020-07-27:新增字段,添加员工所属店铺编号ygdpbm
    public void insertV8Mess(String strFormid, String strYearMonth, List<MessForV8Vo> listMess, RecordSet rs) {
        //数据库操作
        String insertSql = "insert into uf_xsyjhzb (ygdpbm,dpbm,dpmc,ygh,ygxm,gwmc,yrw,xszls,zyzls,zjxs,zyzj,zhejxs,zyzhej,sfzls,formmodeid,modedatacreater,uuid,xzrq,gwdm,ygmc,zwmc,tgzjls,tgzjlss,wxzjls,wxzjlss,sszls,sszjls,sszhjls)" +
                " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        String updateSql = "update uf_xsyjhzb set ygdpbm=?,dpmc=?,ygh=?,ygxm=?,gwmc=?,yrw=?,xszls=?,zyzls=?,zjxs=?,zyzj=?,zhejxs=?,zyzhej=?,sfzls=?,xzrq=?,gwdm=?,ygmc=?,zwmc=?,tgzjls=?,tgzjlss=?,wxzjls=?,wxzjlss=?,sszls=?,sszjls=?,sszhjls=?" +
                " where dpbm=? and ygh=?";
        try {
            //循环插入数据
            for (MessForV8Vo messVo : listMess) {
                String strUuid = UUID.randomUUID().toString().replaceAll("-", "");
                log.info("员工号："+messVo.getStrWorkcode()+";员工所属店铺编码："+messVo.getStrYgssDpCode()+";员工店铺编码："+messVo.getStrSapid());
                boolean isUpdate=isExists(strYearMonth,messVo.getStrYgssDpCode(),messVo.getStrWorkcode(),messVo.getStrSapid());
                //如果存在则更新数据
                if(isUpdate){
                    rs.executeUpdate(updateSql, messVo.getStrYgssDpCode(), messVo.getStrDpid(),
                            messVo.getStrUserid(), messVo.getStrJobid(), messVo.getStrMonthSales(),
                            messVo.getStrCountSales(), messVo.getStrSupCountSales(), messVo.getStrPriceSale(),
                            messVo.getStrSupValSale(), messVo.getStrDiscountSale(), messVo.getStrSupDisSale(),
                            messVo.getStrCountFlag(), strYearMonth, messVo.getStrJobCode(),messVo.getStrUserName(),
                            messVo.getStrJobName(), messVo.getStrTgzj(),messVo.getStrTgzhej(), messVo.getStrWxzj(),
                            messVo.getStrWxzhej(),messVo.getStrSszls(),messVo.getStrSszjls(),messVo.getStrSszhjls(), messVo.getStrSapid(), messVo.getStrWorkcode());
                    log.info("更新-员工号为："+messVo.getStrWorkcode()+";店铺号为："+messVo.getStrSapid()+";员工所属店铺编码："+messVo.getStrYgssDpCode());
                }
                else {
                    rs.executeUpdate(insertSql, messVo.getStrYgssDpCode(), messVo.getStrSapid(), messVo.getStrDpid(), messVo.getStrWorkcode(),
                            messVo.getStrUserid(), messVo.getStrJobid(), messVo.getStrMonthSales(),
                            messVo.getStrCountSales(), messVo.getStrSupCountSales(), messVo.getStrPriceSale(),
                            messVo.getStrSupValSale(), messVo.getStrDiscountSale(), messVo.getStrSupDisSale(),
                            messVo.getStrCountFlag(), strFormid, "1", strUuid, strYearMonth, messVo.getStrJobCode(),
                            messVo.getStrUserName(), messVo.getStrJobName(), messVo.getStrTgzj(),
                            messVo.getStrTgzhej(), messVo.getStrWxzj(), messVo.getStrWxzhej(),messVo.getStrSszls(),messVo.getStrSszjls(),messVo.getStrSszhjls());
                    log.info("插入-人员数据,工号:"+messVo.getStrWorkcode()+";姓名："+messVo.getStrUserName()+";员工所属店铺编码："+messVo.getStrYgssDpCode());
                    //表单建模授权
                    String sql = "select id from uf_xsyjhzb where uuid=?";
                    rs.executeQuery(sql, strUuid);
                    rs.next();
                    ModeRightInfo modeRightInfoBanc = new ModeRightInfo();
                    modeRightInfoBanc.setNewRight(true);
                    // 新建的
                    modeRightInfoBanc.editModeDataShare(1, Integer.parseInt(strFormid), Util.getIntValue(rs.getString("id")));
                    log.info("结束授权--");
                }
            }

        } catch (Exception e) {
            log.info("插入异常:" + e);
        }
    }

    /***
     * 判断建模表中是否存在重复数据
     * @param strYearMonth 年月
     * @param strDpbm_ygss 员工所属店铺编码
     * @param strYgh 员工号
     * @return
     */
    public Boolean isExists(String strYearMonth,String strDpbm_ygss,String strYgh,String strDpbm){
        RecordSet rsResult=new RecordSet();
        String sql = "select 1 from uf_xsyjhzb where dpbm='"+strDpbm+"' " +
                "and ygdpbm='"+strDpbm_ygss+"' and xzrq='"+strYearMonth+"' and ygh='"+strYgh+"' and ygh is not null";
        rsResult.executeQuery(sql);
        Boolean result=false;
        while (rsResult.next()){
            result=true;
        }
        return result;
    }

}
